package bancosys.tec.services.input.driver;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jmine.tec.services.impl.ServicesImplMessages;
import jmine.tec.services.io.ServiceBean;
import jmine.tec.services.io.ServicesPageBean;
import jmine.tec.services.io.ServicesWriter;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;

import bancosys.tec.services.input.XLSFileConstants;
import bancosys.tec.utils.date.Date;
import bancosys.tec.utils.date.DateUtils;

/**
 * ServiceWriter que escreve no formato do Excel (xls).
 * 
 * @author Rafael Volpato
 * @date Aug 4, 2011
 */
public class XLSServiceWriter implements ServicesWriter {

    // The maximum number of characters in a Excel cell is 32767.
    public static final int MAX_CELL_LEN = 32767;

    // The maximum column width in Excel is 255 characters. 255*256 = 65280
    private static final int MAX_WIDTH = 65280;

    private static final byte GRAY_80_PERCENT_COLOR_BLUE = (byte) 161;

    private static final byte GRAY_80_PERCENT_COLOR_GREEN = (byte) 161;

    private static final byte GRAY_80_PERCENT_COLOR_RED = (byte) 161;

    private static final byte GRAY_25_PERCENT_COLOR_BLUE = (byte) 239;

    private static final byte GRAY_25_PERCENT_COLOR_GREEN = (byte) 239;

    private static final byte GRAY_25_PERCENT_COLOR_RED = (byte) 239;

    private static final byte PALE_BLUE_COLOR_BLUE = (byte) 225;

    private static final byte PALE_BLUE_COLOR_GREEN = (byte) 198;

    private static final byte PALE_BLUE_COLOR_RED = (byte) 171;

    private static final short LINHA_SOMA_FINAL_COMENTARIO = 3;

    private static final short COLUNA_INICIAL_COMENTARIO = 2;

    private static final short COLUNA_FINAL_COMENTARIO = 9;

    /** Tamanho que deve ser adicionado as colunas apos o auto-ajuste */
    private static final int TAMANHO_AJUSTE_COLUNAS = 400;

    private static final Pattern DATE_PATTERN = Pattern.compile("^(\\d\\d?)/(\\d\\d?)/(\\d\\d\\d\\d)$");

    private static final Pattern NUMBER_PATTERN = Pattern.compile("^\\-?\\d+(\\.(\\d+))?$");

    private static final String DATE_FORMAT = "m/d/yy";

    /**
     * {@inheritDoc}
     */
    public void writeServices(List<ServicesPageBean> pages, OutputStream outputStream) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();

        // Estilos de celulas utilizados na planilha
        HSSFCellStyle paramNameAllCellStyle = this.createParamNameCellStyle(workbook, HSSFColor.PALE_BLUE.index);
        HSSFCellStyle paramNameValidationCellStyle = this.createParamNameCellStyle(workbook, HSSFColor.LIGHT_GREEN.index);

        HSSFCellStyle paramValueCellStyle = this.createParamValueCellStyle(workbook);
        HSSFCellStyle paramValueCellDateStyle = this.createParamValueDateCellStyle(workbook);
        HSSFCellStyle paramTitleCellStyle = this.createTitleCellStyle(workbook);

        Map<Integer, HSSFCellStyle> paramNumberCellStyleMap = new HashMap<Integer, HSSFCellStyle>();

        // Configurando as cores da planilha
        this.configurePalette(workbook);

        HSSFDataFormat dataFormat = workbook.createDataFormat();

        for (ServicesPageBean page : pages) {
            HSSFSheet sheet = page.getName() != null ? workbook.createSheet(page.getName()) : workbook.createSheet();
            List<ServiceBean> services = page.getServices();
            // Create the drawing patriarch. This is the top level container for all shapes including cell comments.
            HSSFPatriarch patr = sheet.createDrawingPatriarch();
            int rowNumber = 0;

            ServiceBean lastServiceBean = null;
            List<String> parametersOrder = new ArrayList<String>();
            int maxCellPos = 0;
            for (int serviceIndex = 0; serviceIndex < services.size(); serviceIndex++) {
                ServiceBean serviceBean = services.get(serviceIndex);
                int cellPos = -1;
                HSSFRow row = null;
                HSSFCell cell = null;

                Map<String, Object> param = serviceBean.getParameters();
                if ((lastServiceBean == null) || !serviceBean.getServiceFullName().equals(lastServiceBean.getServiceFullName())) {
                    HSSFCellStyle paramNameCellStyle =
                            ((serviceBean.getAction() != null) && serviceBean.getAction().validar()) ? paramNameValidationCellStyle
                                    : paramNameAllCellStyle;
                    // Imprimir uma linha em branco e o header do servico
                    cellPos = 0;
                    sheet.createRow(rowNumber++);
                    row = sheet.createRow(rowNumber++);
                    cell = row.createCell(cellPos++);
                    cell.setCellStyle(paramNameCellStyle);

                    cell = row.createCell(cellPos++);
                    cell.setCellStyle(paramNameCellStyle);

                    Set<String> allowedParameters = new HashSet<String>();
                    int j = serviceIndex;
                    while ((j < services.size()) && serviceBean.getServiceFullName().equals(services.get(j).getServiceFullName())) {
                        allowedParameters.addAll(services.get(j++).getParameters().keySet());
                    }
                    allowedParameters.remove(null);
                    allowedParameters.remove("");
                    parametersOrder = new ArrayList<String>(allowedParameters);
                    Collections.sort(parametersOrder);
                    for (String paramName : parametersOrder) {
                        cell = row.createCell(cellPos++);
                        cell.setCellValue(new HSSFRichTextString(abbreviateForExcel(paramName)));
                        cell.setCellStyle(paramNameCellStyle);
                    }
                    /*
                     * <WORKAROUND> HSSFRow wraps a RowRecord object, that expects the lastCol variable to be the index of the last column
                     * PLUS ONE. HSSFRow, however, sets this value as the index of the last column (forgets the plus one). Most spreadsheet
                     * processors (Excel, Open Office) seem to ignore this variable, but the ServiceXLSDriver doesn't. This extra cell
                     * corrects it, and the generated XLS works on the Driver and on spreadsheet processors. Not the best solution, though.
                     * This seems to be a POI bug, and if it gets fixed, this line can be removed, but it probably won't break the current
                     * code. </WORKAROUND>
                     */
                    row.createCell(cellPos++);
                }

                // Imprimir o conteudo do servico
                row = sheet.createRow(rowNumber++);
                cellPos = XLSFileConstants.ACTIONS_COLUMN;

                cell = row.createCell(cellPos++);

                if ((serviceBean.getComment() != null) && (serviceBean.getComment().trim().length() != 0)) {
                    HSSFComment comment1 =
                            patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, COLUNA_INICIAL_COMENTARIO, (short) rowNumber,
                                    COLUNA_FINAL_COMENTARIO, (short) rowNumber + LINHA_SOMA_FINAL_COMENTARIO));
                    comment1.setString(new HSSFRichTextString(abbreviateForExcel(serviceBean.getComment())));
                    cell.setCellComment(comment1);
                }

                cell.setCellStyle(paramTitleCellStyle);
                cell.setCellValue(new HSSFRichTextString(abbreviateForExcel(serviceBean.getAction() == null ? "" : serviceBean.getAction()
                        .toString())));

                cell = row.createCell(cellPos++);
                cell.setCellStyle(paramTitleCellStyle);
                cell.setCellValue(new HSSFRichTextString(abbreviateForExcel(serviceBean.getName())));

                for (String paramName : parametersOrder) {
                    Object originalValue = param.get(paramName);
                    String value = "";
                    if (originalValue != null) {
                        value = this.convertValue(originalValue);
                    }

                    cell = row.createCell(cellPos++);

                    Matcher dateMatcher = DATE_PATTERN.matcher(value);
                    Matcher numberMatcher = NUMBER_PATTERN.matcher(value);
                    if (dateMatcher.matches()) {
                        Date date = DateUtils.getCalendarByViewDateDDMMYYYY(value);
                        cell.setCellValue(date);
                        cell.setCellStyle(paramValueCellDateStyle);
                    } else if (numberMatcher.matches()) {
                        Integer decimal = numberMatcher.group(2) != null ? numberMatcher.group(2).length() : 0;
                        if (!paramNumberCellStyleMap.containsKey(decimal)) {
                            HSSFCellStyle paramValueCellNumberStyle = this.createParamValueCellStyle(workbook);
                            paramValueCellNumberStyle.setDataFormat(dataFormat.getFormat(String.format(Locale.ENGLISH, "#,##%." + decimal
                                    + "f", 0.0)));
                            paramNumberCellStyleMap.put(decimal, paramValueCellNumberStyle);
                        }
                        cell.setCellValue(Double.parseDouble(value));
                        cell.setCellStyle(paramNumberCellStyleMap.get(decimal));
                    } else {
                        cell.setCellValue(new HSSFRichTextString(abbreviateForExcel(value)));
                        cell.setCellStyle(paramValueCellStyle);
                    }
                }

                if ((serviceBean.getComment() != null) && (serviceBean.getComment().trim().length() != 0)) {
                    cell = row.createCell(cellPos++);
                    cell.setCellValue(new HSSFRichTextString(abbreviateForExcel("#" + serviceBean.getComment().trim())));
                }

                // <REPEAT-WORKAROUND>
                row.createCell(cellPos++);

                lastServiceBean = serviceBean;
                maxCellPos = maxCellPos > cellPos ? maxCellPos : cellPos;
            }

            this.ajustarTamanhoColunas(sheet, maxCellPos);
        }

        // Gravar os dados do workbook no output
        workbook.write(outputStream);
    }

    /**
     * Configures the color palette
     * 
     * @param workbook workbook
     */
    protected void configurePalette(HSSFWorkbook workbook) {
        HSSFPalette palette = workbook.getCustomPalette();
        palette.setColorAtIndex(HSSFColor.GREY_80_PERCENT.index, GRAY_80_PERCENT_COLOR_RED, GRAY_80_PERCENT_COLOR_GREEN,
                GRAY_80_PERCENT_COLOR_BLUE);
        palette.setColorAtIndex(HSSFColor.GREY_25_PERCENT.index, GRAY_25_PERCENT_COLOR_RED, GRAY_25_PERCENT_COLOR_GREEN,
                GRAY_25_PERCENT_COLOR_BLUE);
        palette.setColorAtIndex(HSSFColor.PALE_BLUE.index, PALE_BLUE_COLOR_RED, PALE_BLUE_COLOR_GREEN, PALE_BLUE_COLOR_BLUE);

        palette.setColorAtIndex(HSSFColor.LIGHT_GREEN.index, PALE_BLUE_COLOR_RED, PALE_BLUE_COLOR_BLUE, PALE_BLUE_COLOR_GREEN);
    }

    /**
     * If given text overflows Excel's max cell value, appends a message to the beginning indicating that a overflow has occurred and
     * abbreviates the result to a valid value.
     * 
     * @param text the text
     * @return the treated text.
     */
    public static String abbreviateForExcel(String text) {
        if (text.length() <= MAX_CELL_LEN) {
            return text;
        }
        return StringUtils.abbreviate(ServicesImplMessages.XLS_CHARACTER_LIMIT_OVERFLOW.create(MAX_CELL_LEN, text.length(), text)
                .getMessage(), MAX_CELL_LEN);
    }

    /**
     * Cria um novo estilo de celula que deve ser utilizado nas celulas de titulo (service name, service action).
     * 
     * @param workbook workbook
     * @return estilo de celula
     */
    private HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();

        // Bordas
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        // Cor de fundo
        style.setFillForegroundColor(HSSFColor.GREY_80_PERCENT.index);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        // Fonte
        HSSFFont font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setColor(HSSFColor.WHITE.index);
        style.setFont(font);

        return style;
    }

    /**
     * Cria um novo estilo de celula que deve ser utilizado nas celulas que possuem o valor de parametros.
     * 
     * @param workbook workbook
     * @return estilo de celula
     */
    private HSSFCellStyle createParamValueCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();

        // Bordas
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        // Cor de fundo
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        return style;
    }

    /**
     * Cria um novo estilo de celula que deve ser utilizado nas celulas que possuem o nome de parametros.
     * 
     * @param workbook workbook
     * @param colorIndex colorIndex
     * @return estilo de celula
     */
    private HSSFCellStyle createParamNameCellStyle(HSSFWorkbook workbook, short colorIndex) {
        HSSFCellStyle style = workbook.createCellStyle();

        // Bordas
        style.setBorderBottom(CellStyle.BORDER_MEDIUM_DASHED);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        // Cor de fundo
        style.setFillForegroundColor(colorIndex);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        // Fonte
        HSSFFont font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(font);

        return style;
    }

    /**
     * Cria um novo estilo de celula que deve ser utilizado nas celulas que possuem o valor de parametros (data).
     * 
     * @param workbook workbook
     * @return estilo de celula
     */
    private HSSFCellStyle createParamValueDateCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = this.createParamValueCellStyle(workbook);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        return style;
    }

    /**
     * Ajusta o tamnho das colunas de uma sheet do arquivo.
     * 
     * @param sheet sheet
     * @param qtdeColunas quantidade de colunas da sheet.
     */
    private void ajustarTamanhoColunas(HSSFSheet sheet, int qtdeColunas) {
        for (short i = 0; i < qtdeColunas; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, Math.min(sheet.getColumnWidth((int) i) + TAMANHO_AJUSTE_COLUNAS, MAX_WIDTH));
        }
    }

    /**
     * Converte valores para formato esperado (data/valor em formato pt_BR).
     * 
     * @param value valor original.
     * @return valor convertido.
     */
    private String convertValue(Object value) {
        String convertedValue = String.valueOf(value);
        if (value instanceof Date) {
            convertedValue = DateUtils.dateToStringDDMMYYYY((Date) value);
        } else if (value instanceof BigDecimal) {
            convertedValue = ((BigDecimal) value).toPlainString();
        }
        return convertedValue;
    }
}
